package zy.dao.wx.coupon.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.wx.coupon.WxCouponDAO;
import zy.entity.sell.ecoupon.T_Sell_ECouponList;
import zy.entity.sell.ecoupon.T_Sell_Ecoupon_User;

@Repository
public class WxCouponDAOImpl extends BaseDaoImpl implements WxCouponDAO {

	@Override
	public List<T_Sell_ECouponList> getCouponCenterList(
			Map<String, Object> params) {
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecl_id,ecl_number,ecl_code,ecl_money,ecl_limitmoney,ecl_amount,ec_begindate,ec_enddate, ");
//		sql.append(" (SELECT COUNT(1) FROM t_sell_ecoupon_user ecu WHERE 1=1 AND ecu.ecu_code = ecl.ecl_code AND ecu.companyid = ecl.companyid) AS ecl_receive_amount,");//领取数量
		sql.append(" (SELECT COUNT(1) FROM t_sell_ecoupon_user ecu WHERE 1=1 AND ecu.ecu_code = ecl.ecl_code AND ecu.ecu_tel = :wu_mobile AND ecu.companyid = ecl.companyid) AS ecl_my_count ");
		sql.append(" FROM t_sell_ecouponlist ecl ");
		sql.append(" JOIN t_sell_ecoupon ec ON ec.ec_number = ecl.ecl_number AND ec.companyid = ecl.companyid ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND ecl.companyid = :companyid ");
		sql.append(" AND ec.ec_shop_code = :shop_code ");
		sql.append(" AND ec.ec_state = 1 ");//审核通过
		sql.append(" AND ec.ec_enddate >= CURDATE() ");//未过期
		sql.append(" AND ecl.ecl_amount >  ");//发放数量要大于领取数量  ecl_receive_amount
		sql.append(" (SELECT COUNT(1) FROM t_sell_ecoupon_user ecu WHERE 1=1 AND ecu.ecu_code = ecl.ecl_code AND ecu.companyid = ecl.companyid) ");//领取数量
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
	}

	@Override
	public T_Sell_ECouponList loadDetail(String ecl_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecl_id,ecl_number,ecl_code,ecl_usedmoney,ecl_money,ecl_limitmoney,ecl_amount,ec_begindate,ec_enddate");
		sql.append(" FROM t_sell_ecouponlist t ");
		sql.append(" JOIN t_sell_ecoupon ec ON ec.ec_number = t.ecl_number AND ec.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND ecl_code = :ecl_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ecl_code", ecl_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public Integer countECouponReceived(String ecl_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_ecoupon_user t");
		sql.append(" WHERE ecu_code = :ecu_code");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("ecu_code", ecl_code).addValue("companyid", companyid),Integer.class);
	}

	@Override
	public void save_ecoupon_user(T_Sell_Ecoupon_User t_Sell_Ecoupon_User) {
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO t_sell_ecoupon_user");
		sql.append(" (ecu_ec_number,ecu_code,ecu_date,ecu_name,ecu_tel,ecu_vip_code,ecu_money,ecu_limitmoney,");
		sql.append(" ecu_shop_code,ecu_begindate,ecu_enddate,ecu_state,ecu_check_no,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ecu_ec_number,:ecu_code,:ecu_date,:ecu_name,:ecu_tel,:ecu_vip_code,:ecu_money,:ecu_limitmoney,");
		sql.append(" :ecu_shop_code,:ecu_begindate,:ecu_enddate,:ecu_state,:ecu_check_no,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(t_Sell_Ecoupon_User));
	}

}
